package com.wxzhou.wangzhuan.repository;

import com.wxzhou.wangzhuan.dto.*;
import com.wxzhou.wangzhuan.entity.IncomeSense;
import com.wxzhou.wangzhuan.entity.SimpleUser;
import com.wxzhou.wangzhuan.entity.UserAccountWithUser;
import com.wxzhou.wangzhuan.entity.UserRealnameRelation;
import io.smallrye.mutiny.Uni;
import io.vertx.mutiny.sqlclient.Pool;
import io.vertx.mutiny.sqlclient.Row;
import io.vertx.mutiny.sqlclient.Tuple;
import jakarta.enterprise.context.ApplicationScoped;
import jakarta.inject.Inject;
import org.jboss.logging.Logger;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * 用户账户持久层
 */
@ApplicationScoped
public class UserAccountRepository {

    private static final Logger LOG = Logger.getLogger(UserAccountRepository.class);

    @Inject
    Pool client;

    /**
     * 根据用户ID查找用户账户
     */
    public Uni<UserAccountWithUser> findByUserId(Long userId) {
        String sql = "SELECT a.*,u.nick_name user_nick_name,u.is_enable user_is_enable,u.is_withdrawal user_is_withdrawal FROM wz_user_accounts a JOIN wz_users u ON a.user_id = u.id WHERE user_id = $1 LIMIT 1";
        return client.preparedQuery(sql).execute(Tuple.of(userId)).onItem().transform(rows -> {
            if (rows.size() > 0) {
                return mapRowToUserAccount(rows.iterator().next());
            }
            return null;
        });
    }

    /**
     * 查询用户所有的实名记录
     *
     * @param userid
     * @return
     */
    public Uni<List<UserRealnameRelation>> findRealNameRelationByUser(Long userid) {
        String sql = "SELECT a.* FROM wz_user_real_name_relation a WHERE a.user_id is not null and a.user_id = $1";
        return client.preparedQuery(sql).execute(Tuple.of(userid)).onItem().transform(rows -> {
            List<UserRealnameRelation> configs = new ArrayList<>();
            for (Row row : rows) {
                configs.add(mapRowToRealnameRelation(row));
            }
            return configs;
        });
    }


    /**
     * 插入用户实名结果
     *
     * @param realnameRelation
     * @return
     */
    public Uni<UserRealnameRelation> insertUserRealNameResult(UserRealnameRelation realnameRelation) {

        List<String> insertColumns = Arrays.asList("user_id", "withdraw_channel", "payment_type", "simple_name", "details_info");

        // 列名拼接
        String columns = String.join(", ", insertColumns);

        // 动态生成占位符（$1, $2, $3...）
        List<String> placeholderList = new ArrayList<>();
        for (int i = 0; i < insertColumns.size(); i++) {
            placeholderList.add("$" + (i + 1));
        }
        String placeholders = String.join(", ", placeholderList);

        String sql = String.format(
                "INSERT INTO wz_user_real_name_relation (%s) VALUES (%s)  RETURNING *",
                columns,
                placeholders
        );

        Tuple tuple = Tuple.from(Arrays.asList(realnameRelation.getUserid(), realnameRelation.getWithdrawChannel(), realnameRelation.getPaymentType(), realnameRelation.getSimpleName(), realnameRelation.getDetailsInfo()));

        return client.preparedQuery(sql.toString())
                .execute(tuple)
                .onItem().transformToUni(rows -> Uni.createFrom().item(realnameRelation));
    }


    /**
     * 查询用户收入记录
     *
     * @param pageRequest
     * @return
     */
    public Uni<List<UserIncomeRecordResponseDto>> findWithPagination(UserIncomeRecordRequestDto pageRequest) {
        QueryConditionUtil queryCoinlogStrBy = createQueryStrBy(pageRequest);
        StringBuilder sql = queryCoinlogStrBy.getPageStr();
        List<Object> pageParams = queryCoinlogStrBy.getPageParams();

        return client.preparedQuery(sql.toString()).execute(Tuple.from(pageParams)).onItem().transform(rows -> {
            List<UserIncomeRecordResponseDto> configs = new ArrayList<>();
            for (Row row : rows) {
                UserIncomeRecordResponseDto dto = mapRowToIncomeRecord(row);
                BigDecimal newAmount = dto.getAmount().divide(new BigDecimal("100")).setScale(4, RoundingMode.DOWN);
                dto.setAmount(newAmount);
                configs.add(dto);
            }
            return configs;
        });
    }


    public Uni<Long> countOf(UserIncomeRecordRequestDto pageRequest) {
        QueryConditionUtil queryCoinlogStrBy = createQueryStrBy(pageRequest);
        List<Object> paramList = queryCoinlogStrBy.getParams();

        Tuple params = paramList.isEmpty() ? Tuple.tuple() : Tuple.from(paramList);

        return client.preparedQuery(queryCoinlogStrBy.getQueryStr().toString()).execute(params).onItem().transform(rows -> {
            if (rows.size() > 0) {
                return rows.iterator().next().getLong("count");
            }
            return 0L;
        });
    }

    private QueryConditionUtil createQueryStrBy(UserIncomeRecordRequestDto pageRequest) {

        QueryConditionUtil queryConditionUtil = new QueryConditionUtil();
        //TODO: 从表中查询用户收益记录
        StringBuilder sql = new StringBuilder("SELECT DISTINCT a.*,b.nick_name from_nick_name FROM wz_user_account_changes a LEFT JOIN  wz_users b ON a.user_id = b.id WHERE a.user_id is not null");

        List<Object> params = new ArrayList<>();

        int size = pageRequest.getLimit();
        int offset = pageRequest.getOffset();
        sql.append(" AND a.user_id = $").append(params.size() + 1);
        params.add(pageRequest.getUserid());

        sql.append(" AND a.change_type = $").append(params.size() + 1);
        params.add(AccountChangeType.INCOME.getCode());
        sql.append(" ORDER BY a.created_at DESC ");
        queryConditionUtil.setQueryStr(sql);
        queryConditionUtil.setParams(params);
        List<Object> pageParams = new ArrayList<>();
        pageParams.addAll(params);

        StringBuilder pageSql = new StringBuilder(sql.toString());
        // 分页
        pageSql.append(" LIMIT $").append(pageParams.size() + 1);
        pageParams.add(size);
        pageSql.append(" OFFSET $").append(pageParams.size() + 1);
        pageParams.add(offset);

        queryConditionUtil.setPageStr(pageSql);
        queryConditionUtil.setPageParams(pageParams);

        StringBuilder countStr = new StringBuilder("SELECT COUNT(*) as count FROM  (").append(sql).append(") c ");
        queryConditionUtil.setQueryStr(countStr);
        return queryConditionUtil;
    }


    /**
     * 查询用户某日的收益面板
     *
     * @param pageRequest
     * @return
     */
    public Uni<UserIncomeDashboardResponseDto> findTotalIncomeByRangeTime(UserIncomeDashboardRequestDto pageRequest) {
        QueryConditionUtil queryCoinlogStrBy = createRangeTimeQueryStrBy(pageRequest);
        StringBuilder sql = new StringBuilder("SELECT COALESCE(SUM(a.change_amount), 0) total_amount FROM wz_user_account_changes a WHERE a.user_id is not null  AND a.user_id = $1  AND a.change_type = $2 " +
                "  AND a.created_at >= $3 AND a.created_at <= $4 ");
        List<Object> pageParams = queryCoinlogStrBy.getParams();

        if (pageRequest.getIncomesense() != null && pageRequest.getIncomesense() > 0) {
            sql.append(" AND a.bs_ac = $5");
        }

        return client.preparedQuery(sql.toString()).execute(Tuple.from(pageParams)).onItem().transform(rows -> {
            UserIncomeDashboardResponseDto dto = new UserIncomeDashboardResponseDto();
            if (rows.size() > 0) {
                Row row = rows.iterator().next();
                BigDecimal totalAmount = row.getBigDecimal("total_amount").divide(new BigDecimal("100"),6,RoundingMode.DOWN);
                dto.setTotalAmount(totalAmount);
                return dto;
            }
            return dto;
        });
    }

    public Uni<List<UserIncomeRecordResponseDto>> findIncomeWithPagination(UserIncomeDashboardRequestDto pageRequest) {
        QueryConditionUtil queryCoinlogStrBy = createRangeTimeQueryStrBy(pageRequest);
        StringBuilder sql = queryCoinlogStrBy.getPageStr();
        List<Object> pageParams = queryCoinlogStrBy.getPageParams();

        return client.preparedQuery(sql.toString()).execute(Tuple.from(pageParams)).onItem().transform(rows -> {
            List<UserIncomeRecordResponseDto> configs = new ArrayList<>();
            for (Row row : rows) {
                UserIncomeRecordResponseDto dto = mapRowToIncomeRecord(row);
                dto.setAmount(dto.getAmount().divide(new BigDecimal("100"),6,RoundingMode.DOWN));
                configs.add(dto);
            }
            return configs;
        });
    }


    public Uni<Long> countIncomeOf(UserIncomeDashboardRequestDto pageRequest) {
        QueryConditionUtil queryCoinlogStrBy = createRangeTimeQueryStrBy(pageRequest);
        List<Object> paramList = queryCoinlogStrBy.getParams();

        Tuple params = paramList.isEmpty() ? Tuple.tuple() : Tuple.from(paramList);

        return client.preparedQuery(queryCoinlogStrBy.getQueryStr().toString()).execute(params).onItem().transform(rows -> {
            if (rows.size() > 0) {
                return rows.iterator().next().getLong("count");
            }
            return 0L;
        });
    }

    private QueryConditionUtil createRangeTimeQueryStrBy(UserIncomeDashboardRequestDto pageRequest) {

        QueryConditionUtil queryConditionUtil = new QueryConditionUtil();
        //TODO: 从表中查询用户收益记录
        StringBuilder sql = new StringBuilder("SELECT DISTINCT a.*,b.nick_name from_nick_name FROM wz_user_account_changes a LEFT JOIN  wz_users b ON a.user_id = b.id WHERE a.user_id is not null");

        List<Object> params = new ArrayList<>();

        int size = pageRequest.getLimit();
        int offset = pageRequest.getOffset();
        sql.append(" AND a.user_id = $").append(params.size() + 1);
        params.add(pageRequest.getUserid());

        sql.append(" AND a.change_type = $").append(params.size() + 1);
        params.add(AccountChangeType.INCOME.getCode());

        sql.append(" AND a.created_at >= $").append(params.size() + 1).append(" AND a.created_at <= $").append(params.size() + 2);
        params.add(pageRequest.calculateRange().getStartDateTime());
        params.add(pageRequest.calculateRange().getEndDateTime());

        if (pageRequest.getIncomesense() != null && pageRequest.getIncomesense() > 0) {
            sql.append(" AND a.bs_ac = $").append(params.size() + 1);
            params.add(IncomeSense.fromCode(pageRequest.getIncomesense()).getCode());
        }
        sql.append(" ORDER BY a.created_at DESC ");
        queryConditionUtil.setQueryStr(sql);
        queryConditionUtil.setParams(params);
        List<Object> pageParams = new ArrayList<>();
        pageParams.addAll(params);

        StringBuilder pageSql = new StringBuilder(sql.toString());
        // 分页
        pageSql.append(" LIMIT $").append(pageParams.size() + 1);
        pageParams.add(size);
        pageSql.append(" OFFSET $").append(pageParams.size() + 1);
        pageParams.add(offset);

        queryConditionUtil.setPageStr(pageSql);
        queryConditionUtil.setPageParams(pageParams);

        StringBuilder countStr = new StringBuilder("SELECT COUNT(*) as count FROM  (").append(sql).append(") c ");
        queryConditionUtil.setQueryStr(countStr);
        return queryConditionUtil;
    }


    /**
     * 将数据库行映射到用户账户对象，带用户基本信息和余额
     */
    private UserAccountWithUser mapRowToUserAccount(Row row) {
        UserAccountWithUser account = new UserAccountWithUser();
        account.setId(row.getLong("id"));
        account.setUserId(row.getLong("user_id"));
        account.setBalance(new BigDecimal(row.getLong("balance")));
        account.setFrozenBalance(new BigDecimal(row.getLong("frozen_balance")));
        account.setTotalAmount(new BigDecimal(row.getLong("total_amount")));
        account.setStatus(row.getInteger("status"));
        account.setCreatedAt(row.getLocalDateTime("created_at"));
        account.setUpdatedAt(row.getLocalDateTime("updated_at"));
        account.setUser_nick_name(row.getString("user_nick_name"));
        account.setUser_is_enable(row.getBoolean("user_is_enable"));
        account.setUser_is_withdrawal(row.getBoolean("user_is_withdrawal"));
        return account;
    }

    private UserIncomeRecordResponseDto mapRowToIncomeRecord(Row row) {
        UserIncomeRecordResponseDto account = new UserIncomeRecordResponseDto();
        account.setAmount(row.getBigDecimal("change_amount"));
        Long fromUserId = row.getLong("from_user_id");
        String fromNickName = row.getString("from_nick_name");
        if (fromUserId != null && fromUserId > 0L) {
            account.setFromUser(new SimpleUser(fromUserId, fromNickName));
        }
        Integer bsAc = row.getInteger("bs_ac");
        IncomeSense incomeSense = IncomeSense.fromCode(bsAc);
        account.setTitle(incomeSense.getDescription());
        account.setOccTime(row.getLocalDateTime("created_at"));
        account.setRemark(row.getString("remark")); //用户端描述
        return account;
    }

    private UserRealnameRelation mapRowToRealnameRelation(Row row) {
        UserRealnameRelation account = new UserRealnameRelation();
        account.setUserid(row.getLong("user_id"));
        account.setPaymentType(row.getInteger("payment_type")); //用户端描述
        account.setWithdrawChannel(row.getInteger("withdraw_channel")); //用户端描述
        account.setSimpleName(row.getString("simple_name")); //用户端描述
        return account;
    }
}